psql is awesome!

PostgresTV
2022-08-18

Who am I

• Lætitia Avrot
• mydbanotebook.org
• PostgreSQL recognized Contributor
• founder of #PostgresWomen
• EDB Field CTO
• @l_avrot

Why ?

More difficult to learn but...
• Feature-rich
• "Recording"
• Scripting
• More efficient
• Cooler

My workspace

• Tmux (integrated into .zshrc)
• tmux-logging
• split screen vim/psql

My workspace

Connecting

          $ psql --host localhost --port 5432 --username laetitia \
--dbname test
psql (15devel)
Type "help" for help.

test=# 

Host

$ psql --host localhost

$ psql -h localhost

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
on host "localhost" (address "::1") at port "5432".

Host

$ export PGHOST=localhost
$ psql

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
on host "localhost" (address "::1") at port "5432".

Host

$ export PGHOST=
$ psql

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".

Host

$ psql -h /tmp

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".

Port

$ psql --port 5433

$ psql -p 5433

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5433".

Port

$ export PGPORT=5433
$ psql

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5433".

Port

$ export PGPORT=
$ psql

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".

User

$ psql --username test

$ psql -U test

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "test"
via socket in "/tmp" at port "5432".

User

$ export PGUSER=test
$ psql

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "test"
via socket in "/tmp" at port "5432".

User

$ export PGUSER=
$ psql

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".

Database

$ psql test

$ psql --dbname test

$ psql -d test

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "laetitia"
via socket in "/tmp" at port "5432".

Database

$ export PGDATABASE=test
$ psql

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "laetitia"
via socket in "/tmp" at port "5432".

Database

$ export PGDATABASE=
$ psql

psql (15devel)
Type "help" for help.

laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".

Database

$ psql postgresql://laetitia@localhost:5433/test

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "laetitia"
on host "localhost" (address "::1") at port "5433".

Database

$ psql "user=laetitia host=localhost port=5433 dbname=test"

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "laetitia"
on host "localhost" (address "::1") at port "5433".

Service file

$ cat ~/.pg_service.conf
[mydb]
host=localhost
# Port is same as default but I could use port=5432
user=test
dbname=laetitia

          psql service=mydb
psql (15devel)
Type "help" for help.

laetitia=> \conninfo
You are connected to database "laetitia" as user "test"
on host "localhost" (address "::1") at port "5432".

Shell (or batch) mode

$ psql -c "select * from test;"

id |  value   
----+----------
  1 | Use
  2 | your
  3 | fear...
  4 | it
  5 | can
  6 | take
  7 | you
  8 | to
  9 | the
 10 | place
 11 | where
 12 | you
 13 | store
 14 | your
 15 | courage.
(15 rows)

$ psql -c "IDENTIFY_SYSTEM" \
-d "replication=true dbname=postgres"

      systemid       | timeline |  xlogpos  | dbname 
---------------------+----------+-----------+--------
 6980320202536645744 |        1 | 0/16BD170 | 
(1 row)

$ cat query.sql
select * from test;

$ psql -f query.sql

id |  value   
----+----------
  1 | Use
  2 | your
  3 | fear...
  4 | it
  5 | can
  6 | take
  7 | you
  8 | to
  9 | the
 10 | place
 11 | where
 12 | you
 13 | store
 14 | your
 15 | courage.
(15 rows)

Interactive mode

$ psql "user=test dbname=laetitia"
psql (15devel)
Type "help" for help.

laetitia=>

Explaining prompt

laetitia=>

laetitia=> select
laetitia-> 

laetitia=> (
laetitia(> 

laetitia-> '
laetitia'> 

laetitia-> "
laetitia"> 

Explaining prompt

laetitia=> begin;
BEGIN
laetitia=*>

$ psql
psql (15devel)
Type "help" for help.

laetitia=#

The basics

laetitia=# \q

laetitia=# exit

laetitia=# help

laetitia=# \?

laetitia=# \h

Settings

\set variable value

\pset variable value

\setenv variable value

Settings

laetitia#=\set settingname wal
laetitia#=select name, setting from pg_settings
laetitia-#where name ~ :'settingname';
             name              |    setting
-------------------------------+---------------
 max_slot_wal_keep_size        | -1
 max_wal_senders               | 10
 max_wal_size                  | 1024
 min_wal_size                  | 80
 track_wal_io_timing           | off
 wal_block_size                | 8192
 wal_buffers                   | 512
 wal_compression               | off
 wal_consistency_checking      | 
 wal_init_zero                 | on
 wal_keep_size                 | 0
 wal_level                     | replica
 wal_log_hints                 | off
 wal_receiver_create_temp_slot | off
 wal_receiver_status_interval  | 10
 wal_receiver_timeout          | 60000
 wal_recycle                   | on
 wal_retrieve_retry_interval   | 5000
 wal_segment_size              | 16777216
 wal_sender_timeout            | 60000
 wal_skip_threshold            | 2048
 wal_sync_method               | open_datasync
 wal_writer_delay              | 200
 wal_writer_flush_after        | 128
(24 rows)

Settings

laetitia:~$ cat query3.sql 
 select name, setting
 from pg_settings
 where name ~ :'settingname';

laetitia:~$ psql --variable "settingname=wal" -f query3.sql 
             name              |    setting    
-------------------------------+---------------
 max_slot_wal_keep_size        | -1
 max_wal_senders               | 10
 max_wal_size                  | 1024
 min_wal_size                  | 80
 track_wal_io_timing           | off
 wal_block_size                | 8192
 wal_buffers                   | 512
 wal_compression               | off
 wal_consistency_checking      | 
 wal_init_zero                 | on
 wal_keep_size                 | 0
 wal_level                     | replica
 wal_log_hints                 | off
 wal_receiver_create_temp_slot | off
 wal_receiver_status_interval  | 10
 wal_receiver_timeout          | 60000
 wal_recycle                   | on
 wal_retrieve_retry_interval   | 5000
 wal_segment_size              | 16777216
 wal_sender_timeout            | 60000
 wal_skip_threshold            | 2048
 wal_sync_method               | open_datasync
 wal_writer_delay              | 200
 wal_writer_flush_after        | 128
(24 rows)

Settings

laetitia=# select name from pg_settings limit 10;
            name            
----------------------------
 allow_in_place_tablespaces
 allow_system_table_mods
 application_name
 archive_cleanup_command
 archive_command
 archive_library
 archive_mode
 archive_timeout
 array_nulls
 authentication_timeout
(10 rows)

laetitia=# select :ROW_COUNT;
 ?column? 
----------
       10
(1 row)

Settings

laetitia#=\set COMP_KEYWORD_CASE lower

laetitia#=\set ECHO_HIDDEN

laetitia#=\set SHOW_ALL_RESULTS

Settings

\pset pager off

laetitia#=\setenv PSQL_EDITOR vim

Settings

laetitia=# \pset null '🦄'
Null display is "🦄".

laetitia=# select null;
 ?column? 
----------
 🦄
(1 row)

Getting information

laetitia=# \l 

laetitia=# \d 

laetitia=# \dn 

laetitia=# \dt 

laetitia=# \df 

Getting information

laetitia=# \set ECHO_HIDDEN on 

laetitia=# \dt test
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN
  'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's'
  THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN
'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','t','s','')
  AND c.relname OPERATOR(pg_catalog.~) '^(test)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | test | table | laetitia
(1 row)

Working everyday with psql

laetitia=# \x

laetitia=# \g

laetitia=# select * from pg_settings where name='max_wal_senders';
      name       | setting | unit |           category            |
                               short_desc                                |
 extra_desc |  context   | vartype | source  | min_val | max_val |
 enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart 
-----------------+---------+------+-------------------------------+
-------------------------------------------------------------------------+
------------+------------+---------+---------+---------+---------+
----------+----------+-----------+------------+------------+-----------------
 max_wal_senders | 10      |      | Replication / Sending Servers |
 Sets the maximum number of simultaneously running WAL sender processes. |
            | postmaster | integer | default | 0       | 262143  |
          | 10       | 10        |            |            | f
(1 row)

Working everyday with psql

laetitia=# \gx
-[ RECORD 1 ]---+------------------------------------------------------------------------
name            | max_wal_senders
setting         | 10
unit            | 
category        | Replication / Sending Servers
short_desc      | Sets the maximum number of simultaneously running
WAL sender processes.
extra_desc      | 
context         | postmaster
vartype         | integer
source          | default
min_val         | 0
max_val         | 262143
enumvals        | 
boot_val        | 10
reset_val       | 10
sourcefile      | 
sourceline      | 
pending_restart | f

Working everyday with psql

laetitia=# \pset format html
Output format is html.
laetitia=# \g
<table border="1">
  <tr>
    <th align="center">name</th>
    <th align="center">setting</th>
    <th align="center">unit</th>
    <th align="center">category</th>
    <th align="center">short_desc</th>
    <th align="center">extra_desc</th>
    <th align="center">context</th>
    <th align="center">vartype</th>
    <th align="center">source</th>
    <th align="center">min_val</th>
    <th align="center">max_val</th>
    <th align="center">enumvals</th>
    <th align="center">boot_val</th>
    <th align="center">reset_val</th>
    <th align="center">sourcefile</th>
    <th align="center">sourceline</th>
    <th align="center">pending_restart</th>
  </tr>
  <tr valign="top">
    <td align="left">max_wal_senders</td>
    <td align="left">10</td>
    <td align="left">  </td>
    <td align="left">Replication / Sending Servers</td>
    <td align="left">Sets the maximum number of simultaneously running WAL sender processes.</td>
    <td align="left">  </td>
    <td align="left">postmaster</td>
    <td align="left">integer</td>
    <td align="left">default</td>
    <td align="left">0</td>
    <td align="left">262143</td>
    <td align="left">  </td>
    <td align="left">10</td>
    <td align="left">10</td>
    <td align="left">  </td>
    <td align="right">  </td>
    <td align="left">f</td>
  </tr>
</table>
<p>(1 row)<br />
</p>

Working everyday with psql

laetitia=# \pset format unaligned
Output format is unaligned.
laetitia=# \g
name|setting|unit|category|short_desc|extra_desc|context|vartype|source|min_val|max_val|enumvals|boot_val|reset_val|sourcefile|sourceline|pending_restart
max_wal_senders|10||Replication / Sending Servers|Sets the maximum number of simultaneously running WAL sender processes.||postmaster|integer|default|0|262143||10|10|||f
(1 row)

Working everyday with psql

laetitia=# \pset format csv
Output format is unaligned.
laetitia=# \g
name,setting,unit,category,short_desc,extra_desc,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline,pending_restart
max_wal_senders,10,,Replication / Sending Servers,Sets the maximum number of simultaneously running WAL sender processes.,,postmaster,integer,default,0,262143,,10,10,,,f
(1 row)

Working everyday with psql

laetitia=# \o query_out.csv
laetitia=# \g
laetitia=# \! cat query_out.csv
name,setting,unit,category,short_desc,extra_desc,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline,pending_restart
max_wal_senders,10,,Replication / Sending Servers,Sets the maximum number of simultaneously running WAL sender processes.,,postmaster,integer,default,0,262143,,10,10,,,f

Working everyday with psql

laetitia=# \! cat query.sql
select * from test;
laetitia=# \i query.sql 
laetitia=# \! cat query_out.csv
name,setting,unit,category,short_desc,extra_desc,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline,pending_restart
max_wal_senders,10,,Replication / Sending Servers,Sets the maximum number of simultaneously running WAL sender processes.,,postmaster,integer,default,0,262143,,10,10,,,f
id,value
1,Use
2,your
3,fear...
4,it
5,can
6,take
7,you
8,to
9,the
10,place
11,where
12,you
13,store
14,your
15,courage.

History

Getting deeper

laetitia=# select count(*) from pg_stat_activity where state = 'idle';
 count 
-------
     0
(1 row)

laetitia=# \watch 1
Wed Jul 14 11:59:56 2021 (every 1s)

 count 
-------
     0
(1 row)

Wed Jul 14 11:59:57 2021 (every 1s)

 count 
-------
     0
(1 row)

Getting deeper

laetitia=# select sum(setting) from pg_settings
laetitia-# where name ~ 'buffer';
ERROR:  function sum(text) does not exist
LINE 1: select sum(setting) from pg_settings where name ~ 'buffer';
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

laetitia=# select setting from pg_settings
laetitia-# where name ~ 'buffer';
 setting 
---------
 16384
 1024
 512
(3 rows)

Getting deeper

laetitia=# \gdesc
 Column  | Type 
---------+------
 setting | text
(1 row)

laetitia=# select sum(setting::integer) from pg_settings
laetitia-# where name ~ 'buffer';
  sum  
-------
 17920
(1 row)

Getting deeper

laetitia=# begin;
BEGIN
laetitia=*# \dt test
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | test | table | laetitia
(1 row)

laetitia=*# select 'alter table ' || tablename
 || ' owner to test;'
laetitia-*# from pg_tables
laetitia-*# where tableowner = 'laetitia';
            ?column?             
---------------------------------
 alter table test owner to test;
(1 row) 

Getting deeper

laetitia=*# \gexec
ALTER TABLE

laetitia=*# \dt test
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | test | table | test
(1 row)

laetitia=*# rollback;
ROLLBACK

This is the end